package dao.search;

import bean.search.StaySearch;
import util.C3p0Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 住宿查询数据交互实现类
 **/

public class StaySearchDao {

    public List<StaySearch> staySearch(int rNumber){

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<StaySearch> ss = null;

        String sql = "select r_number,r_sex,s_number,s_name,s_tel,i_key,i_intime,i_outtime,i_gimoney,i_acmoney from t_room\n" +
                "left join t_student on t_room.r_number = t_student.s_r_number\n" +
                "left join t_incheck on t_room.r_number = t_incheck.i_number where r_number = ?;";

        try {
            conn = C3p0Util.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,rNumber);
            rs = ps.executeQuery();
            while(rs.next()) {
                if (ss == null) {
                    ss = new ArrayList<StaySearch>();
                }
                StaySearch staySearch = new StaySearch();
                staySearch.setrNumber(rs.getInt("r_number"));
                staySearch.setrSex(rs.getString("r_sex"));
                staySearch.setsNumber(rs.getInt("s_number"));
                staySearch.setsName(rs.getString("s_name"));
                staySearch.setTel(rs.getInt("s_tel"));
                staySearch.setiKey(rs.getString("i_key"));
                staySearch.setInTime(rs.getDate("i_intime"));
                staySearch.setOutTime(rs.getDate("i_outtime"));
                staySearch.setGiMoney(rs.getInt("i_gimoney"));
                staySearch.setAcMoney(rs.getInt("i_acmoney"));
                ss.add(staySearch);

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return ss;
    }

}
